package iq.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;


public class TestConn {

	/**
	 * @author dingdahai
	 * @date Nov 25, 2009 10:31:38 AM
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			new TestConn().testConn();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	public void testConn() throws ClassNotFoundException, SQLException {
		// 加载jdbc-odbc驱动程序
		Class.forName("oracle.jdbc.driver.OracleDriver");
		// 通过数据源连接：
		String url = "jdbc:odbc:YourDSN";
		url = "jdbc:oracle:thin:@192.168.0.253:1521:orcljk";
		String login = "jjkk";
		String password = "jjkk";
		Connection conn = DriverManager.getConnection(url, login, password);
		// 通过机器名或IP连接：
		// String url = "jdbc:odbc:Driver={SQL
		// Server};Server=imagebear;uid=sa;pwd=;Database=jspdb";
		// 如果用IP只需将imagebear改为192.168.0.36即可
		// Connection conn = DriverManager.getConnection(url);
		// 如果url中不包含uid和pwd，也可写成：
		// Connection conn = DriverManager.getConnection(url,"sa","");
		// 如果是Access数据库只需将url改为：
		// String url = "jdbc:odbc:Driver={Microsoft Access
		// Driver(*.mdb)};DBQ=c:/data/test.mdb";

		String sql = "select count(*) from newkjbiao2";
		PreparedStatement pstm = conn.prepareStatement(sql);

		//ResultSet rs = pstm.executeQuery("SELECT * FROM tbl_test");
		
		ResultSet rs = pstm.executeQuery();
		rs.next();
		System.out.println(rs.getInt(1));
		// 关闭Statement对象：
		rs.close();// 关闭ResultSet对象,不关闭的话，执行语句多了会抛cursor异常
		rs = null;

		pstm.close();// 关闭Statement对象：
		pstm = null;

		conn.close();
	}

	/**
	 * Re: ORA-01000: maximum open cursors exceeded
	 * 
	 * Returns the number of open cursors on an Oracle Database.
	 * 
	 * @return Number of currently open cursors - note that this method will
	 *         produce one of these but it cleanly closes it so will not
	 *         accumulate them.
	 * @param enhanceConnection
	 *            Connection to the Oracle Database
	 */
	public static int getCurrentOpenCursors(Connection conn) {
		PreparedStatement psQuery = null;
		ResultSet rs = null;

		int cursors = -1;
		try {
			String sqlQuery = "select count(*) AS COUNT from v$open_cursor where user_name like '<PUT YOUR DB USERNAME HERE>'";
			Connection enhanceConnection = null;
			psQuery = enhanceConnection.prepareStatement(sqlQuery);
			rs = psQuery.executeQuery();

			if (rs.next()) {
				cursors = rs.getInt("COUNT");
			}
		} catch (SQLException e) {
			System.out
					.println("SQLException in getCurrentOpenCursors(Connection conn): "
							+ e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (psQuery != null) {
					psQuery.close();
				}
			} catch (SQLException ex) {
				System.out
						.println("A SQLException error has occured in getCurrentOpenCursors(Connection conn): "
								+ ex.getMessage());
				ex.printStackTrace();
			}
		}
		return cursors;
	}

	public void execute() throws ClassNotFoundException, SQLException {
		Class.forName(com.mysql.jdbc.Driver.class.getName());
		
		java.sql.Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.1.150:3306/qxcard?characterEncoding=utf8", "ecard", "ecard");
		conn.setAutoCommit(false);
		
		Statement stmt = conn.createStatement();
		PreparedStatement pstmt = conn.prepareStatement("update kinghh_user set zipcode=? where user_id=?");
		
		java.sql.ResultSet rs = stmt.executeQuery("select * from kinghh_user where is_delete='n'");//只取未被删除的
		
		try {
			while(rs.next()) {
				String zipcode = rs.getString("zipcode");
				if (zipcode != null && !"".equals(zipcode)) {
					continue;
				}
				Long userId = rs.getLong("user_id");
				zipcode = "aaa" + userId + "aaa";
				
				pstmt.setString(1, zipcode);
				pstmt.setLong(2, userId);
				pstmt.execute();
			}
			
		} catch (Exception e) {
			conn.rollback();
			e.printStackTrace();
		}
		
		conn.commit();
	}
}
